""" This is the database control module. """

import sqlite3 as sql, glob, os, random

class db:
  """ This is how we control the database. """
  name = 'img/database' # the name of the database file
  autoprune = "false" # only autoprune if we're allowed
  resevoir = "true"   # only use the resevoir if we're allowed
  forcerotate = "false" # automatically rotate out old files
  poolsize = 60     # how many pictures to keep in the img folder
  howmany = 20      # how many results to show per page in 'view'
  
  def getcursor(self):
    data = sql.connect(self.name) # connect to the database
    cur = data.cursor() # create our database cursor
    return (data,cur)
  
  def getsettings(self):
    """ Get the current settings from the database. If any settings aren't set, fill them in."""
    (data,cur) = self.getcursor()
    cur.execute('SELECT name,value FROM settings;') # get all the settings
    rows = cur.fetchall()
    settings = {'autoprune':None,'resevoir':None,'forcerotate':None,'poolsize':None,'howmany':None}
    for row in rows:
      settings[row[0]] = row[1]
    if(settings['autoprune'] == None):
      settings['autoprune'] = "false"
      cur.execute('INSERT INTO settings(name, value) VALUES ("autoprune","false");')
    if(settings['resevoir'] == None):
      settings['resevoir'] = "true"
      cur.execute('INSERT INTO settings(name, value) VALUES ("resevoir","true");')
    if(settings['forcerotate'] == None):
      settings['forcerotate'] = "false"
      cur.execute('INSERT INTO settings(name, value) VALUES ("forcerotate","false");')
    if(settings['poolsize'] == None):
      settings['poolsize'] = "60"
      cur.execute('INSERT INTO settings(name, value) VALUES ("poolsize","60");')
    if(settings['howmany'] == None):
      settings['howmany'] = "20"
      cur.execute('INSERT INTO settings(name, value) VALUES ("howmany","20");')
    data.commit()
    data.close()
    return (settings['autoprune'],settings['resevoir'],settings['forcerotate'],settings['poolsize'],settings['howmany'])
  
  def setsettings(self,autoprune='false',resevoir='true',forcerotate='false',poolsize="60",howmany="20"):
    """ Set the current settings in the database. """
    (data,cur) = self.getcursor()
    cur.execute('UPDATE settings SET value = "%s" WHERE name = "autoprune";' % (autoprune))
    cur.execute('UPDATE settings SET value = "%s" WHERE name = "resevoir";' % (resevoir))
    cur.execute('UPDATE settings SET value = "%s" WHERE name = "forcerotate";' % (forcerotate))
    cur.execute('UPDATE settings SET value = "%s" WHERE name = "poolsize";' % (poolsize))
    cur.execute('UPDATE settings SET value = "%s" WHERE name = "howmany";' % (howmany))
    self.autoprune = autoprune
    self.resevoir = resevoir
    self.forcerotate = forcerotate
    self.poolsize = int(poolsize)
    self.howmany = int(howmany)
    data.commit()
    data.close()
    self.update()
  
  def autoretire(self,minbattles=10,rating=80):
    """ Check the database to see if any images ought to be automatically retired. """
    pruned = 0
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename, wins, losses FROM images WHERE rating >= %d;' % rating) # get all the high-rated images
    rows = cur.fetchall() # put them in a table
    for row in rows: # iterate the table
      filename = row[0].split('/')[-1] # get the filename
      battles = int(row[1]) + int(row[2])
      if(battles >= minbattles):
        try:
          os.rename(row[0],"retired/%s" % (filename)) # move that file
          pruned += 1
        except:
          print " !!! ERROR RETIRING FILE %s" % row[0]
    data.close()
    return pruned
  
  def autodrop(self,minbattles=10,rating=20):
    """ Check the database to see if any images ought to be automatically dropped. """
    dropped = 0
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename, wins, losses FROM images WHERE rating <= %d;' % rating) # get all the low-rated images
    rows = cur.fetchall() # put them in a table
    for row in rows: # iterate the table
      battles = int(row[1]) + int(row[2])
      if(battles >= minbattles):
        try:
          os.remove(row[0])
          dropped += 1
        except:
          print " !!! ERROR REMOVING FILE %s" % row[0]
    data.close()
    return dropped
  
  def resethigh(self):
    """ Search for images with more than 30 votes. Knock them back into the resevoir. """
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename, wins, losses FROM images;')
    rows = cur.fetchall()
    dropped = 0
    for row in rows:
      battles = int(row[1]) + int(row[2])
      if(battles >= 30):
        filename = row[0].split('/')[-1]
        try:
          os.rename(row[0],"resevoir/%s" % filename)
          cur.execute('DELETE FROM images WHERE filename = "%s";' % (row[0]))
          dropped += 1
        except:
          print" !!! ERROR RESETTING FILE %s" % row[0]
    data.commit()
    data.close()
    return dropped
  
  def drop(self,filename):
    """ Drop an image from the database and delete it from the system. """
    try:
      os.remove(filename)
    except:
      print("Error: Could not delete file %s." % (filename))
    self.update()
  
  def retire(self):
    """ Retire the top 20% of rated images. """
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename, wins, losses FROM images WHERE rating >= 80;') # get all the high-rated images
    rows = cur.fetchall() # put them in a table
    for row in rows: # iterate that table
      filename = row[0].split('/')[-1] # get the filename
      battles = int(row[1]) + int(row[2])
      if(battles >= 5):
        try:
          os.rename(row[0],"retired/%s" % (filename)) # move that file
        except:
          print("-- Error deleting file %s" % filename)
    data.close()
    self.update() # update to remove entries from database
  
  def purge(self):
    """ Purge the bottom 20% of rated images. """
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename, wins, losses FROM images WHERE rating <= 20;') # get all the low-rated images
    rows = cur.fetchall() # put them in a table
    for row in rows: # iterate that table
      battles = int(row[1]) + int(row[2])
      if(battles >= 5):
        os.remove(row[0]) # delete that file
    data.close()
    self.update() # update to remove entries from database
  
  def pick(self):
    """ Return two filenames at random to battle. """
    # first we'll want to use files that haven't been rated yet.
    files = [] # list of filenames
    (data,cur) = self.getcursor()
    cur.execute('SELECT filename FROM images WHERE wins=0 AND losses=0 ORDER BY RANDOM() LIMIT 2;')
    rows = cur.fetchall()
    for row in rows: # get the filenames from the returned rows
      files.append(row[0]) # append them to the list
    if len(files) < 2: # if there are less than two files, we gotta dive into the rated files.
      cur.execute('SELECT filename FROM images WHERE wins > 0 OR losses > 0 ORDER BY RANDOM() LIMIT %i;' % (2 - len(files)))
      rows = cur.fetchall()
      for row in rows:
        files.append(row[0]) # append the filenames to the list.
    data.close()
    return files
   
  def vote(self,winner,loser):
    """ Increment the winner's wins and the loser's losses, then update their
        ratings. """
    # increment the wins and losses for both images
    (data,cur) = self.getcursor()
    cur.execute('UPDATE images SET wins = wins + 1 WHERE filename = "%s";' % (winner))
    cur.execute('UPDATE images SET losses = losses + 1 WHERE filename = "%s";' % (loser))
    data.commit()
    # update each image's ratings
    self.fixratings(winner)
    self.fixratings(loser)
    dropped = 0
    if(self.autoprune == "true"):
      dropped += self.autodrop(10,20)
      dropped += self.autoretire(10,80)
    if(self.forcerotate == "true"):
      dropped += self.autodrop(20,30)
      dropped += self.autoretire(20,70)
      dropped += self.resethigh()
    if(dropped != 0):
      self.update()
    data.close()
    
  def fixratings(self,filename):
    """ Fix the rating for an individual file. """
    (data,cur) = self.getcursor()
    cur.execute('SELECT * FROM images WHERE filename="%s";' % (filename)) # get file wins and losses
    row = cur.fetchone() # fetch a row
    wins = float(row[1]) # set wins
    losses = float(row[2]) # set losses
    battles = wins + losses # calculate battles
    rating = float(wins/battles) # get win percentage
    cur.execute('UPDATE images SET rating=%d WHERE filename="%s";' % (rating * 100,filename)) # update file in database
    data.commit()
    data.close()
  
  def add(self,filename):
    """ Adds a filename to the database. """
    (data,cur) = self.getcursor()
    cur.execute('INSERT INTO images VALUES ("%s",0,0,0);' % (filename))
    data.commit()
    data.close()
  
  def rem(self,filename):
    """ Removes a filename from the database. """
    (data,cur) = self.getcursor()
    cur.execute('DELETE FROM images WHERE filename="%s";' % (filename))
    data.commit()
    data.close()
  
  def toplist(self,howmany,start):
    """ Returns the top howmany results beginning with start, based on rating.
    """
    (data,cur) = self.getcursor()
    cur.execute('SELECT * FROM images ORDER BY rating DESC,wins DESC,losses ASC;') # get a list of items organized by their rating, best first
    rows = cur.fetchall() # get that list
    finish = start + howmany # for the second part of the list subset
    subset = rows[start:finish] # get the subset we need
    data.close()
    return subset
  
  def images(self,directory):
    """ Retrieve a list of all images from the img folder. """
    try:
      # get a list of files
      extensions = ["jpg","jpeg","gif","png","svg"]
      files = []
      for ext in extensions:
        files += glob.glob("%s/*.%s" % (directory,ext))
      random.shuffle(files)
      return files # return the list
    except:
      print "--- No images in the 'img' directory."
      return None # return nothing
  
  def entries(self):
    """ List all images in the database. """
    try:
      files = []
      (data,cur) = self.getcursor()
      cur.execute('SELECT * FROM images;') # get a list of files
      entries = cur.fetchall() # retrieve the entire list
      data.close()
      for entry in entries:
        files.append(entry[0]) # we only need the filenames
      return files # return the list
    except:
      print "--- No entries in database."
      return None # return nothing
  
  def rowcount(self):
    (data,cur) = self.getcursor()
    cur.execute('SELECT Count(*) FROM images;')
    rows = cur.fetchone()
    data.close()
    print "-- There are currently %s images in the database." % (rows[0])
  
  def update(self):
    """ Scan the img folder for files and compare it against the database,
        then make corrections as necessary. """
    print "- Updating database..."
    print "-- Checking for new settings..."
    (data,cur) = self.getcursor()
    cur.execute('SELECT name,value FROM settings;')
    rows = cur.fetchall()
    for row in rows:
      if(row[0] == "autoprune"):
        self.autoprune = row[1]
      if(row[0] == "resevoir"):
        self.resevoir = row[1]
      if(row[0] == "forcerotate"):
        self.forcerotate = row[1]
      if(row[0] == "poolsize"):
        self.poolsize = int(row[1])
      if(row[0] == "howmany"):
        self.howmany = int(row[1])
    data.close()
    print "-- Settings updated."
    images = self.images("img") # get a list of all files in the img folder
    if(self.resevoir == "true"):
      # let's get new images for the battle
      if(len(images) < self.poolsize):
        thismany = self.poolsize - len(images)
        resevoir = self.images("resevoir")
        if(len(resevoir) < thismany):
          thismany = len(resevoir)
        for x in range(0,thismany):
          filename = resevoir[x].split('/')[-1]
          os.rename("resevoir/%s" % filename,"img/%s" % filename)
      images = self.images("img")
    print "-- Checking for new or removed files..."
    entries = self.entries() # get a list of all files in the database
    if images == None:
      images = [] # empty list for no files
    if entries == None:
      entries = [] # empty list for no entries
    added = 0   # how many files were added to the database
    removed = 0 # how many files were removed from the database
    for image in images: # for all files,
      if image not in entries: # check to see if they're in the database
        self.add(image) # if the image isn't in the database, add an entry for it
        added = added + 1 # increment added
    print "--- %i files added." % (added)
    for image in entries: # for all entries,
      if image not in images: # check to see if any don't match up with files
        self.rem(image) # if the file doesn't exist, remove it from the database
        removed = removed + 1 # increment removed
    print "--- %i files removed." % (removed)
    print "-- Database update complete."
    self.rowcount()
  
  def __init__(self):
    """ Initialize our database. """
    print "Initializing database..."
    (data,cur) = self.getcursor()
    cur.execute('SELECT name FROM sqlite_master WHERE type = "table";') # check for tables
    if cur.fetchone() == None: # if there are no tables, create them
      print "- Brand new database, creating structure..."
      cur.execute('CREATE TABLE images(filename TEXT, wins INTEGER, losses INTEGER, rating INTEGER);') # create images table
      cur.execute('CREATE TABLE settings(name TEXT, value TEXT);')
      cur.execute('INSERT INTO settings(name, value) VALUES ("autoprune","false");')
      cur.execute('INSERT INTO settings(name, value) VALUES ("resevoir","true");')
      cur.execute('INSERT INTO settings(name, value) VALUES ("forcerotate","false");')
      cur.execute('INSERT INTO settings(name, value) VALUES ("poolsize","60");')
      cur.execute('INSERT INTO settings(name, value) VALUES ("howmany","20");')
      data.commit()
    data.close()
    self.update() # update our image database
    print "- Database initialization complete."